package org.java.jdbc.evolution.jdbc.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.java.jdbc.evolution.jdbc.po.User;

/**
 * 用户表接口实现
 */
public class UserDaoImp implements UserDao {

	// 定义数据库驱动
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	// 定义数据地址
	static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/db-evoluation?useUnicode=true&characterEncoding=UTF-8";

	// 定义登录名称
	String username = "root";
	String password = "root";

	public int insertUser(User user) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL, username, password);
			String sql = "insert into user(nickName, account, password, updateTime, createTime) values(?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			int index = 1;
			ps.setString(index++, user.getNickName());
			ps.setString(index++, user.getAccount());
			ps.setString(index++, user.getPassword());
			ps.setDate(index++, new Date(user.getUpdateTime().getTime()));
			ps.setDate(index, new Date(user.getCreateTime().getTime()));
			return ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	public int updateUser(User user) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			// 注册驱动
			Class.forName(JDBC_DRIVER);
			// 获取数据库连接
			conn = DriverManager.getConnection(DB_URL, username, password);
			String sql = "update user set nickName =?, account =?, password =?, updateTime =?, createTime =? where id =?";
			ps = conn.prepareStatement(sql);
			int index = 1;
			ps.setString(index++, user.getNickName());
			ps.setString(index++, user.getAccount());
			ps.setString(index++, user.getPassword());
			ps.setDate(index++, new Date(user.getUpdateTime().getTime()));
			ps.setDate(index++, new Date(user.getCreateTime().getTime()));
			ps.setInt(index, user.getId());
			return ps.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	public User selectUserById(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 注册驱动
			Class.forName(JDBC_DRIVER);
			// 获取数据库连接
			conn = DriverManager.getConnection(DB_URL, username, password);
			String sql = "select * from user where id =?";
			ps = conn.prepareStatement(sql);
			int index = 1;
			ps.setInt(index, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setNickName(rs.getString("nickName"));
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setUpdateTime(rs.getDate("updateTime"));
				u.setCreateTime(rs.getDate("createTime"));
				return u;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (rs != null)
					rs.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public List<User> selectUserList() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 注册驱动
			Class.forName(JDBC_DRIVER);
			// 获取数据库连接
			conn = DriverManager.getConnection(DB_URL, username, password);
			String sql = "select * from user";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			List<User> users = null;
			while (rs.next()) {
				if(users == null){
					users = new ArrayList<User>();
				}
				User u = new User();
				u.setId(rs.getInt("id"));
				u.setNickName(rs.getString("nickName"));
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setUpdateTime(rs.getDate("updateTime"));
				u.setCreateTime(rs.getDate("createTime"));
				users.add(u);
			}
			return users;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (rs != null)
					rs.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

}
